﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[_Old_ViewRelatedEntitiesbyEntityID]
 @EntityID int,
 @UserID int

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @EntityNames TABLE (EntityID int, EntityName NVARCHAR(MAX)) 
	DECLARE @ThisEntityType AS NVARCHAR(255), @ThisEntitySubType as NVARCHAR(255)

	DECLARE db_cursor CURSOR FOR
	 (SELECT dbo.DefinedEntityTypes.EntityType, NULL as EntitySubType
		FROM dbo.DefinedEntityTypes)
	 UNION
	 (SELECT dbo.DefinedEntityTypes.EntityType, dbo.DefinedEntitySubTypes.EntitySubType
		FROM dbo.DefinedEntityTypes INNER JOIN
           dbo.DefinedEntitySubTypes ON dbo.DefinedEntityTypes.EntityType = dbo.DefinedEntitySubTypes.EntityType)

	OPEN db_cursor  
	FETCH NEXT FROM db_cursor INTO @ThisEntityType, @ThisEntitySubType  
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
	    INSERT INTO @EntityNames
		EXECUTE ViewEntityNamesByEntityType @ThisEntityType, @ThisEntitySubType
		FETCH NEXT FROM db_cursor INTO @ThisEntityType, @ThisEntitySubType 
	END
	CLOSE db_cursor
	DEALLOCATE db_cursor 

	SELECT * FROM
	(
	SELECT  EntityNames.EntityID AS Entity1ID, EntityNames.EntityName AS Entity1Name,
				 dbo.EntityRelationships.RelationshipName, 
            EntityNames2.EntityID AS Entity2ID, EntityNames2.EntityName AS Entity2Name
			    	FROM  @EntityNames As EntityNames INNER JOIN
                         dbo.EntityRelationships ON EntityNames.EntityID = dbo.EntityRelationships.Entity1ID
						  INNER JOIN
                         @EntityNames AS EntityNames2 ON dbo.EntityRelationships.Entity2ID = EntityNames2.EntityID
	UNION
	SELECT  EntityNames.EntityID AS Entity1ID, EntityNames.EntityName AS Entity1Name,
				 dbo.DefinedEntityRelationships.ReverseRelationship AS RelationshipName,
            EntityNames2.EntityID AS Entity2ID, EntityNames2.EntityName AS Entity2Name
			    	FROM  @EntityNames As EntityNames INNER JOIN
                         dbo.EntityRelationships ON EntityNames.EntityID = dbo.EntityRelationships.Entity2ID
						  INNER JOIN
                         @EntityNames AS EntityNames2 ON dbo.EntityRelationships.Entity1ID = EntityNames2.EntityID
						 INNER JOIN
                         dbo.DefinedEntityRelationships ON dbo.EntityRelationships.RelationshipName = dbo.DefinedEntityRelationships.RelationshipName
			WHERE DefinedEntityRelationships.ReverseRelationship is not null
			) as M
			WHERE M.Entity1ID = @EntityID
	END
